using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using System.Data.Linq;
using System.Linq.Expressions;
using System.Data.SqlClient;
using Acme.Northwind.EFDAL;
using Acme.Northwind.EFDAL.Entity;
using nHydrate.EFCore.DataAccess;
using nHydrate.EFCore.Exceptions;

namespace Acme.Northwind.EFDAL
{
	#region LinqSQLField

	[System.CodeDom.Compiler.GeneratedCode("nHydrateModelGenerator", "5.0.1.100")]
	internal class LinqSQLField
	{
		public string Name { get; set; }
		public string Alias { get; set; }
		public string Table { get; set; }

		public LinqSQLField(string name, string alias, string table)
		{
			this.Name = name.Replace("[", string.Empty).Replace("]", string.Empty);
			this.Alias = alias.Replace("[", string.Empty).Replace("]", string.Empty);
			this.Table = table.Replace("[", string.Empty).Replace("]", string.Empty);
		}

		public string GetSQL(bool useAlias)
		{
			if (useAlias)
				return "[" + this.Table + "].[" + this.Name + "] AS [" + this.Alias + "]";
			else
				return "[" + this.Table + "].[" + this.Name + "]";
		}

		public override string ToString()
		{
			return GetSQL(true);
		}

	}

	#endregion

	#region LinqSQLFromClause

	[System.CodeDom.Compiler.GeneratedCode("nHydrateModelGenerator", "5.0.1.100")]
	internal class LinqSQLFromClause
	{
		public string TableName { get; set; }
		public string Schema { get; set; }
		public string Alias { get; set; }
		public string LinkClause { get; set; }
		public string AnchorAlias { get; set; }

		public LinqSQLFromClause(string tableName, string schema, string alias, string linkClause)
		{
			this.AnchorAlias = string.Empty;
			this.Schema = schema;
			this.TableName = tableName.Replace("[", string.Empty).Replace("]", string.Empty);
			this.Alias = alias.Replace("[", string.Empty).Replace("]", string.Empty);
			this.LinkClause = linkClause;
		}
	}

	[System.CodeDom.Compiler.GeneratedCode("nHydrateModelGenerator", "5.0.1.100")]
	internal class LinqSQLFromClauseCollection : List<LinqSQLFromClause>
	{
		public bool ContainsTable(string tableName)
		{
			foreach (var item in this)
			{
				if (item.TableName == tableName) return true;
			}
			return false;
		}

		public LinqSQLFromClause GetByTable(string tableName)
		{
			foreach (var item in this)
			{
				if (item.TableName == tableName) return item;
			}
			return null;
		}

		public LinqSQLFromClause GetByAlias(string alias)
		{
			alias = alias.Replace("[", string.Empty).Replace("]", string.Empty);
			foreach (var item in this)
			{
				if (item.Alias == alias) return item;
			}
			return null;
		}

		public string GetBaseAliasTable(string parentAlias, string baseTable)
		{
			LinqSQLFromClause root = GetByAlias(parentAlias);
			foreach (var item in this)
			{
				if (item.AnchorAlias == root.TableName)
				{
					if (item.TableName == baseTable) return item.Alias;
				}
			}
			return parentAlias;
		}

		internal string NewAlias()
		{
			int index = 1;
			foreach (var item in this)
			{
				if (item.Alias.StartsWith("z"))
				{
					int current = int.Parse(item.Alias.Substring(1, item.Alias.Length - 1));
					if (current >= index) index = current + 1;
				}
			}
			return "z" + index;
		}

		internal void RemapFromClause()
		{
			//Remap the FROM clause
			foreach (LinqSQLFromClause fromClause in this)
			{
				//Loop through all child tables and remap the "parent" fields to the real child fields
				if ((fromClause.AnchorAlias == string.Empty) && (!string.IsNullOrEmpty(fromClause.LinkClause)))
				{
					string s = fromClause.LinkClause.Substring(3, fromClause.LinkClause.Length - 3);
					string[] arr1 = s.Split('=');

					string[] tfield1 = arr1[0].Trim().Split('.');
					string[] tfield2 = arr1[1].Trim().Split('.');

					string tableAlias1 = tfield1[0];
					string table1 = this.GetByAlias(tableAlias1).TableName;
					string field1 = tfield1[1];

					string tableAlias2 = tfield2[0];
					string table2 = this.GetByAlias(tableAlias2).TableName;
					string field2 = tfield2[1];

					string realTable = string.Empty;

					realTable = GetRealTableName(table1, field1);
					if (realTable != table1)
					{
						string parentAlias = GetByTable(table2).Alias;
						string chlidAlias = GetByTable(realTable).Alias;
						fromClause.LinkClause = fromClause.LinkClause.Replace("[" + parentAlias + "]." + field2, "[" + chlidAlias + "]." + field2);
					}

					realTable = GetRealTableName(table2, field2);
					if (realTable != table2)
					{
						string parentAlias = GetByTable(table2).Alias;
						string chlidAlias = GetByTable(realTable).Alias;
						fromClause.LinkClause = fromClause.LinkClause.Replace("[" + parentAlias + "]." + field2, "[" + chlidAlias + "]." + field2);
					}

				}
			}
		}

		internal string GetRealTableName(string parentTable, string field)
		{
			field = field.Replace("[", string.Empty).Replace("]", string.Empty);
			string realTable = string.Empty;
			if (parentTable == "Categories") realTable = Acme.Northwind.EFDAL.Entity.Category.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "CustomerCustomerDemo") realTable = Acme.Northwind.EFDAL.Entity.CustomerCustomerDemo.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "CustomerDemographics") realTable = Acme.Northwind.EFDAL.Entity.CustomerDemographic.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Customers") realTable = Acme.Northwind.EFDAL.Entity.Customer.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Employees") realTable = Acme.Northwind.EFDAL.Entity.Employee.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "EmployeeTerritories") realTable = Acme.Northwind.EFDAL.Entity.EmployeeTerritorie.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Order Details") realTable = Acme.Northwind.EFDAL.Entity.OrderDetail.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Orders") realTable = Acme.Northwind.EFDAL.Entity.Order.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Products") realTable = Acme.Northwind.EFDAL.Entity.Product.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Region") realTable = Acme.Northwind.EFDAL.Entity.Region.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Shippers") realTable = Acme.Northwind.EFDAL.Entity.Shipper.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Suppliers") realTable = Acme.Northwind.EFDAL.Entity.Supplier.GetTableFromFieldNameSqlMapping(field);
			else if (parentTable == "Territories") realTable = Acme.Northwind.EFDAL.Entity.Territory.GetTableFromFieldNameSqlMapping(field);
			LinqSQLFromClause sqlFromClause = this.GetByTable(realTable);
			return sqlFromClause.TableName;
		}

	}

	#endregion

	#region BusinessObjectQuery

	delegate string GetDatabaseFieldNameDelegate(string field);
	[System.CodeDom.Compiler.GeneratedCode("nHydrateModelGenerator", "5.0.1.100")]
	internal partial class BusinessObjectQuery<T, K, P>
		where T : class, nHydrate.EFCore.DataAccess.IBusinessObject
		where K : class, nHydrate.EFCore.DataAccess.IBusinessObjectLINQQuery
	{
		private const int DEFAULTTIMEOUT = 30;
		#region GetSum
		public static double? GetSum(
			Expression<Func<K, P>> select, 
			Expression<Func<K, bool>> where, 
			nHydrate.EFCore.DataAccess.QueryOptimizer optimizer, 
			string leafTable,
			GetDatabaseFieldNameDelegate getField,
			LinqSQLParser.ObjectTypeConstants objectType)
		{
			var dc = new DataContext(Acme.Northwind.EFDAL.DBHelper.GetConnection());
			var template = dc.GetTable<K>();
			var cmd = BusinessEntityQuery.GetCommand<K, P>(dc, template, select, where);
			cmd.CommandTimeout = DEFAULTTIMEOUT;
			var parser = LinqSQLParser.Create(cmd.CommandText, objectType);
			var fieldName = parser.GetSelectClause();
			cmd.CommandText = "SELECT SUM([" + parser.GetTableAlias(fieldName, leafTable) + "]." + fieldName + ") " + 
				parser.GetFromClause(optimizer) + " " + 
				parser.GetWhereClause() + string.Empty;
			dc.Connection.Open();
			object p = cmd.ExecuteScalar();
			dc.Connection.Close();
			if (p == System.DBNull.Value)
				return null;
			else if (p is decimal)
				return (double?)(decimal)p;
			else if (p is int)
				return (double?)(int)p;
			else if (p is long)
				return (double?)(long)p;
			else if (p is float)
				return (double?)(float)p;
			else if (p is Single)
				return (double?)(Single)p;
			else
				return (double?)p;
		}
		#endregion

		#region GetCount
		public static P GetCount(
			Expression<Func<K, bool>> where,
			nHydrate.EFCore.DataAccess.QueryOptimizer optimizer)
		{
			var dc = new DataContext(Acme.Northwind.EFDAL.DBHelper.GetConnection());
			var template = dc.GetTable<K>();
			var cmd = BusinessEntityQuery.GetCommand<K>(dc, template, where);
			cmd.CommandTimeout = DEFAULTTIMEOUT;
			var parser = LinqSQLParser.Create(cmd.CommandText, LinqSQLParser.ObjectTypeConstants.Table);
			cmd.CommandText = "SELECT COUNT(*) " + parser.GetFromClause(optimizer) + " " + parser.GetWhereClause() + string.Empty;
			dc.Connection.Open();
			object p = cmd.ExecuteScalar();
			dc.Connection.Close();
			return (P)p;
		}
		#endregion

		#region GetDistinct
		public static IEnumerable<P> GetDistinct(
			Expression<Func<K, P>> select, 
			Expression<Func<K, bool>> where,
			nHydrate.EFCore.DataAccess.QueryOptimizer optimizer,
			string leafTable,
			GetDatabaseFieldNameDelegate getField,
			LinqSQLParser.ObjectTypeConstants objectType)
		{
			var dc = new DataContext(Acme.Northwind.EFDAL.DBHelper.GetConnection());
			var template = dc.GetTable<K>();
			var cmd = BusinessEntityQuery.GetCommand<K, P>(dc, template, select, where);
			cmd.CommandTimeout = DEFAULTTIMEOUT;
			var parser = LinqSQLParser.Create(cmd.CommandText, objectType);
			string fieldName = parser.GetSelectClause();
			cmd.CommandText = "SELECT DISTINCT([" + parser.GetTableAlias(fieldName, leafTable) + "]." + fieldName + ") " + parser.GetFromClause(optimizer) + " " + parser.GetWhereClause() + string.Empty;
			dc.Connection.Open();
			var dr = cmd.ExecuteReader();
			var retval = new List<P>();
			while (dr.Read())
			{
				if (!dr.IsDBNull(0))
				{
					object o = dr[0];
					retval.Add((P)o);
				}
			}
			dc.Connection.Close();
			return retval;
		}
		#endregion

		#region GetMin
		public static P GetMin(
			Expression<Func<K, P>> select,
			Expression<Func<K, bool>> where,
			nHydrate.EFCore.DataAccess.QueryOptimizer optimizer,
			string leafTable,
			GetDatabaseFieldNameDelegate getField,
			LinqSQLParser.ObjectTypeConstants objectType)
		{
			var dc = new DataContext(Acme.Northwind.EFDAL.DBHelper.GetConnection());
			var template = dc.GetTable<K>();
			var cmd = BusinessEntityQuery.GetCommand<K, P>(dc, template, select, where);
			cmd.CommandTimeout = DEFAULTTIMEOUT;
			var parser = LinqSQLParser.Create(cmd.CommandText, objectType);
			var fieldName = parser.GetSelectClause();
			cmd.CommandText = "SELECT MIN([" + parser.GetTableAlias(fieldName, leafTable) + "]." + fieldName + ") " + 
				parser.GetFromClause(optimizer) + " " + 
				parser.GetWhereClause() + string.Empty;
			dc.Connection.Open();
			object p = cmd.ExecuteScalar();
			dc.Connection.Close();
			if (p == System.DBNull.Value)
				return default(P);
			else
				return (P)p;
		}
		#endregion

		#region GetMax
		public static P GetMax(
			Expression<Func<K, P>> select,
			Expression<Func<K, bool>> where,
			nHydrate.EFCore.DataAccess.QueryOptimizer optimizer,
			string leafTable,
			GetDatabaseFieldNameDelegate getField,
			LinqSQLParser.ObjectTypeConstants objectType)
		{
			var dc = new DataContext(Acme.Northwind.EFDAL.DBHelper.GetConnection());
			var template = dc.GetTable<K>();
			var cmd = BusinessEntityQuery.GetCommand<K, P>(dc, template, select, where);
			cmd.CommandTimeout = DEFAULTTIMEOUT;
			var parser = LinqSQLParser.Create(cmd.CommandText, objectType);
			string fieldName = parser.GetSelectClause();
			cmd.CommandText = "SELECT MAX([" + parser.GetTableAlias(fieldName, leafTable) + "]." + fieldName + ") " + 
				parser.GetFromClause(optimizer) + " " + 
				parser.GetWhereClause() + string.Empty;
			dc.Connection.Open();
			object p = cmd.ExecuteScalar();
			dc.Connection.Close();
			if (p == System.DBNull.Value)
				return default(P);
			else
				return (P)p;
		}
		#endregion

		#region GetAverage
		public static P GetAverage(
			Expression<Func<K, P>> select,
			Expression<Func<K, bool>> where,
			nHydrate.EFCore.DataAccess.QueryOptimizer optimizer,
			string leafTable,
			GetDatabaseFieldNameDelegate getField,
			LinqSQLParser.ObjectTypeConstants objectType)
		{
			var dc = new DataContext(Acme.Northwind.EFDAL.DBHelper.GetConnection());
			var template = dc.GetTable<K>();
			var cmd = BusinessEntityQuery.GetCommand<K, P>(dc, template, select, where);
			cmd.CommandTimeout = DEFAULTTIMEOUT;
			var parser = LinqSQLParser.Create(cmd.CommandText, objectType);
			var fieldName = parser.GetSelectClause();
			cmd.CommandText = "SELECT AVG([" + parser.GetTableAlias(fieldName, leafTable) + "]." + fieldName + ") " +
				parser.GetFromClause(optimizer) + " " +
				parser.GetWhereClause() + string.Empty;
			dc.Connection.Open();
			object p = cmd.ExecuteScalar();
			dc.Connection.Close();
			if (p == System.DBNull.Value)
				return default(P);
			else
				return (P)p;
		}
		#endregion

		#region UpdateData
		public static int UpdateData(
			Expression<Func<K, P>> select,
			Expression<Func<K, bool>> where,
			P newValue,
			string leafTable,
			GetDatabaseFieldNameDelegate getField,
			bool hasModifyAudit
			)
		{
			var dc = new DataContext(Acme.Northwind.EFDAL.DBHelper.GetConnection());
			var template = dc.GetTable<K>();
			var cmd = BusinessEntityQuery.GetCommand<K, P>(dc, template, select, where);
			cmd.CommandTimeout = DEFAULTTIMEOUT;
			var parser = LinqSQLParser.Create(cmd.CommandText, LinqSQLParser.ObjectTypeConstants.Table);
			var fieldName = parser.GetSelectClause();
			var sql = "UPDATE [" + parser.GetTableAlias(fieldName, leafTable) + "]\r\n";
			sql += "SET [" + parser.GetTableAlias(fieldName, leafTable) + "].[" + fieldName + "] = @newValue\r\n";
			if (hasModifyAudit && (fieldName != "ModifiedBy")) sql += ", [" + parser.GetTableAlias(fieldName, leafTable) + "].[ModifiedBy] = NULL\r\n";
			if (hasModifyAudit && (fieldName != "ModifiedDate")) sql += ", [" + parser.GetTableAlias(fieldName, leafTable) + "].[ModifiedDate] = getdate()\r\n";
			sql += parser.GetFromClause(new nHydrate.EFCore.DataAccess.QueryOptimizer()) + "\r\n";
			sql += parser.GetWhereClause();
			sql += ";select @@rowcount";
			cmd.CommandText = sql;
			if (newValue == null) cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("newValue", System.DBNull.Value));
			else cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("newValue", newValue));
			dc.Connection.Open();
			object p = cmd.ExecuteScalar();
			dc.Connection.Close();
			return (int)p;
		}
		#endregion

	}

	#endregion

	#region LinqSQLParser

	[System.CodeDom.Compiler.GeneratedCode("nHydrateModelGenerator", "5.0.1.100")]
	internal partial class LinqSQLParser
	{

		internal enum ObjectTypeConstants
		{
			Table,
			View,
			StoredProcedure,
		}

		#region Static Creator

		public static LinqSQLParser Create(string sql, ObjectTypeConstants type)
		{
			return new LinqSQLParser(sql, type);
		}

		public static LinqSQLParser Create(string sql, object paging, ObjectTypeConstants type)
		{
			return new LinqSQLParser(sql, paging, type);
		}

		#endregion

		#region Class Members

		private object _paging = null;
		private string _whereClause = string.Empty;
		private string _sql = string.Empty;
		private LinqSQLFromClauseCollection _fromLinkList = new LinqSQLFromClauseCollection();
		private List<LinqSQLField> _fieldList = new List<LinqSQLField>();
		private ObjectTypeConstants _type;

		#endregion

		#region Constructor

		private LinqSQLParser(string sql, object paging, ObjectTypeConstants type)
			: this(sql, type)
		{
			_type = type;
			_paging = paging;
		}

		private LinqSQLParser(string sql, ObjectTypeConstants type)
		{
			if ((sql == null) || (sql == string.Empty))
				throw new Exception("SQL cannot be empty.");

			_type = type;
			_sql = sql;

			//Parse the string into SELECT, FROM, WHERE
			int index = sql.IndexOf("\r\nFROM") + 2;
			string selectClause = sql.Substring(0, index).Replace("\r\n", " ");
			index = sql.IndexOf("\r\nWHERE");
			if (index == -1) index = sql.Length;
			string fromClause = sql.Substring(selectClause.Length - 1, index - selectClause.Length + 1).Replace("\r\n", " ").Trim();
			fromClause = fromClause.Substring(5, fromClause.Length - 5).Trim();
			selectClause = selectClause.Trim();
			fromClause = fromClause.Trim();

			index = sql.IndexOf("\r\nWHERE");
			if (index != -1)
			{
				_whereClause = sql.Substring(index + 2, sql.Length - index - 2).Replace("\r\n", " ");
				_whereClause = _whereClause.Substring(6, _whereClause.Length - 6);
			}

			ParseFrom(fromClause);
			ParseSelect(selectClause);
			RemapParentChild();

		}

		#endregion

		#region RemapParentChild

		private void RemapParentChild()
		{
			//Include all base tables
			LinqSQLFromClauseCollection childTables = new LinqSQLFromClauseCollection();
			foreach (LinqSQLFromClause fromClause in _fromLinkList)
			{
				//Do all field replacements
				if (_type == ObjectTypeConstants.Table)
				{
					if (fromClause.TableName == "Categories")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "CustomerCustomerDemo")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "CustomerDemographics")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Customers")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Employees")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "EmployeeTerritories")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Order Details")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Orders")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Products")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Region")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Shippers")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Suppliers")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Territories")
					{
						childTables.Add(fromClause);
					}
				}
				if (_type == ObjectTypeConstants.View)
				{
					if (fromClause.TableName == "Alphabetical list of products")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Category Sales for 1997")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Current Product List")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Customer and Suppliers by City")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Invoices")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Order Details Extended")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Order Subtotals")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Orders Qry")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Product Sales for 1997")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Products Above Average Price")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Products by Category")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Quarterly Orders")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Sales by Category")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Sales Totals by Amount")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Summary of Sales by Quarter")
					{
						childTables.Add(fromClause);
					}
					if (fromClause.TableName == "Summary of Sales by Year")
					{
						childTables.Add(fromClause);
					}
				}
			}
			_fromLinkList.Clear();
			_fromLinkList.AddRange(childTables);

			//Process the FROM clause
			_fromLinkList.RemapFromClause();

			//Now map the columns to the proper tables
			foreach (var field in _fieldList)
			{
				LinqSQLFromClause clause = _fromLinkList.FirstOrDefault(x => x.Alias == field.Table);
				string realTable = string.Empty;
				if (_type == ObjectTypeConstants.Table)
				{
					if (clause.TableName == "Categories") realTable = Acme.Northwind.EFDAL.Entity.Category.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "CustomerCustomerDemo") realTable = Acme.Northwind.EFDAL.Entity.CustomerCustomerDemo.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "CustomerDemographics") realTable = Acme.Northwind.EFDAL.Entity.CustomerDemographic.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Customers") realTable = Acme.Northwind.EFDAL.Entity.Customer.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Employees") realTable = Acme.Northwind.EFDAL.Entity.Employee.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "EmployeeTerritories") realTable = Acme.Northwind.EFDAL.Entity.EmployeeTerritorie.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Order Details") realTable = Acme.Northwind.EFDAL.Entity.OrderDetail.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Orders") realTable = Acme.Northwind.EFDAL.Entity.Order.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Products") realTable = Acme.Northwind.EFDAL.Entity.Product.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Region") realTable = Acme.Northwind.EFDAL.Entity.Region.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Shippers") realTable = Acme.Northwind.EFDAL.Entity.Shipper.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Suppliers") realTable = Acme.Northwind.EFDAL.Entity.Supplier.GetTableFromFieldAliasSqlMapping(field.Alias);
					else if (clause.TableName == "Territories") realTable = Acme.Northwind.EFDAL.Entity.Territory.GetTableFromFieldAliasSqlMapping(field.Alias);
				}
				LinqSQLFromClause sqlFromClause = _fromLinkList.GetByTable(realTable);
				field.Table = sqlFromClause.Alias;
			}

			//Calculate the WHERE clause
			if (!string.IsNullOrEmpty(_whereClause))
			{
				foreach (LinqSQLFromClause fromClause in _fromLinkList)
				{
					//Only process table that were original and not inserted above
					if (fromClause.AnchorAlias == string.Empty)
						_whereClause = GetRemappedLinqSql(fromClause, _whereClause, _fromLinkList, _type);
				}

			}

		}

		public static string GetRemappedLinqSql(LinqSQLFromClause tableInfo, string whereClause, LinqSQLFromClauseCollection fromLinkList, ObjectTypeConstants type)
		{
			if (type == ObjectTypeConstants.Table)
			{
				switch (tableInfo.TableName)
				{
					case "Categories": return Acme.Northwind.EFDAL.Entity.Category.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "CustomerCustomerDemo": return Acme.Northwind.EFDAL.Entity.CustomerCustomerDemo.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "CustomerDemographics": return Acme.Northwind.EFDAL.Entity.CustomerDemographic.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Customers": return Acme.Northwind.EFDAL.Entity.Customer.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Employees": return Acme.Northwind.EFDAL.Entity.Employee.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "EmployeeTerritories": return Acme.Northwind.EFDAL.Entity.EmployeeTerritorie.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Order Details": return Acme.Northwind.EFDAL.Entity.OrderDetail.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Orders": return Acme.Northwind.EFDAL.Entity.Order.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Products": return Acme.Northwind.EFDAL.Entity.Product.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Region": return Acme.Northwind.EFDAL.Entity.Region.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Shippers": return Acme.Northwind.EFDAL.Entity.Shipper.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Suppliers": return Acme.Northwind.EFDAL.Entity.Supplier.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
					case "Territories": return Acme.Northwind.EFDAL.Entity.Territory.GetRemappedLinqSql(whereClause, tableInfo.Alias, fromLinkList);
				}
			}
			if (type == ObjectTypeConstants.View)
			{
			}
			if (type == ObjectTypeConstants.StoredProcedure)
			{
			}
			throw new Exception("Table not found '" + tableInfo.TableName + "'.");
		}

		#endregion

		#region ParseFrom

		private void ParseFrom(string fromClause)
		{
			const string LINKSQL = "LEFT OUTER JOIN ";
			var fromArray = fromClause.Split(new string[] { LINKSQL }, StringSplitOptions.RemoveEmptyEntries);
			var index = fromArray[0].IndexOf("]");
			var tableName = fromArray[0].Substring(1, index - 1);

			_fromLinkList.Add(new LinqSQLFromClause(tableName, GetSchema(tableName), "t0", string.Empty));

			for (int ii = 1; ii < fromArray.Length; ii++)
			{
				var rawText = fromArray[ii];

				//Get Table
				index = rawText.IndexOf("]");
				var table = rawText.Substring(0, index + 1);
				rawText = rawText.Substring(table.Length + 4, rawText.Length - table.Length - 4);

				//Get Alias
				index = rawText.IndexOf("]");
				var alias = rawText.Substring(0, index + 1);

				//Get Link
				var linkClause = rawText.Substring(alias.Length + 1, rawText.Length - alias.Length - 1);

				_fromLinkList.Add(new LinqSQLFromClause(table, GetSchema(table), alias, linkClause));
			}
		}

		#endregion

		#region ParseSelect

		private void ParseSelect(string selectClause)
		{
			//Parse fields
			var selectList = selectClause.Substring(7, selectClause.Length - 7).Split(',');
			foreach (string rawField in selectList)
			{
				var s = rawField.Trim();

				//Get Table
				var index = s.IndexOf("]");
				var table = s.Substring(0, index + 1);
				s = s.Substring(table.Length + 1, s.Length - table.Length - 1);

				//Get Name
				index = s.IndexOf("]");
				var field = s.Substring(0, index + 1);

				//Get Alias
				var alias = field;
				bool hasAlias = false;
				if (s.Length > field.Length)
				{
					alias = s.Substring(field.Length + 4, s.Length - field.Length - 4);
					hasAlias = true;
				}

				if (!hasAlias)
				{
					LinqSQLFromClause clause = _fromLinkList.FirstOrDefault(x => x.Alias == table.Replace("[", string.Empty).Replace("]", string.Empty));
					switch (clause.TableName)
					{
						case "Categories": alias = Acme.Northwind.EFDAL.Entity.Category.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "CustomerCustomerDemo": alias = Acme.Northwind.EFDAL.Entity.CustomerCustomerDemo.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "CustomerDemographics": alias = Acme.Northwind.EFDAL.Entity.CustomerDemographic.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Customers": alias = Acme.Northwind.EFDAL.Entity.Customer.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Employees": alias = Acme.Northwind.EFDAL.Entity.Employee.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "EmployeeTerritories": alias = Acme.Northwind.EFDAL.Entity.EmployeeTerritorie.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Order Details": alias = Acme.Northwind.EFDAL.Entity.OrderDetail.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Orders": alias = Acme.Northwind.EFDAL.Entity.Order.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Products": alias = Acme.Northwind.EFDAL.Entity.Product.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Region": alias = Acme.Northwind.EFDAL.Entity.Region.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Shippers": alias = Acme.Northwind.EFDAL.Entity.Shipper.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Suppliers": alias = Acme.Northwind.EFDAL.Entity.Supplier.GetFieldAliasFromFieldNameSqlMapping(field); break;
						case "Territories": alias = Acme.Northwind.EFDAL.Entity.Territory.GetFieldAliasFromFieldNameSqlMapping(field); break;
					}
				}

				_fieldList.Add(new LinqSQLField(field, alias, table));
			}

		}

		#endregion

		#region Properties

		#endregion

		#region Methods

		public string GetSchema(string tableName)
		{
			//There is only one schema for this API, so just hard-wire it
			return "dbo";
		}

		public string GetSelectClause()
		{
			var sb = new StringBuilder();
			foreach (var field in _fieldList)
			{
				sb.Append(field.Name);
				if (_fieldList.IndexOf(field) < _fieldList.Count - 1)
					sb.Append(", ");
			}
			return sb.ToString();
		}

		public string GetFromClause(nHydrate.EFCore.DataAccess.QueryOptimizer optimizer)
		{
			var sb = new StringBuilder();

			//Calculate the FROM clause
			int index = 0;
			sb.Append("FROM ");
			foreach (LinqSQLFromClause fromClause in _fromLinkList)
			{
				sb.Append("[" + fromClause.Schema + "].[" + fromClause.TableName + "] AS [" + fromClause.Alias + "] ");
				if (optimizer.NoLocking) sb.Append("WITH (NOLOCK) ");
				if (!string.IsNullOrEmpty(fromClause.LinkClause)) sb.Append(fromClause.LinkClause + " ");

				if (index < _fromLinkList.Count - 1)
				{
					sb.AppendLine();
					sb.Append("LEFT OUTER JOIN ");
				}

				index++;
			}
			sb.AppendLine();
			return sb.ToString();
		}

		public string GetWhereClause()
		{
			if (!string.IsNullOrEmpty(_whereClause)) return "WHERE " + _whereClause;
			else return string.Empty;
		}

		public string GetTableAlias(string fieldName, string tableName)
		{
			return (from x in _fieldList where x.Name == fieldName select x).FirstOrDefault().Table;
		}

		#endregion

		#region GetTextFromResource

		public static string GetTextFromResource(string resourceFileName)
		{
			try
			{
				var sb = new StringBuilder();
				var asm = Assembly.GetExecutingAssembly();
				var manifestStream = asm.GetManifestResourceStream(resourceFileName);
				try
				{
					var theReader = new BinaryReader(manifestStream);
					var theFileRead = new byte[manifestStream.Length];
					manifestStream.Read(theFileRead, 0, theFileRead.Length);
					var data = Encoding.ASCII.GetString(theFileRead);
					theReader.Close();
					return data;
				}
				catch { }
				finally
				{
					manifestStream.Close();
				}
				return string.Empty;
			}
			catch
			{
				throw;
			}
		}

		#endregion

	}

	#endregion

	[System.CodeDom.Compiler.GeneratedCode("nHydrateModelGenerator", "5.0.1.100")]
	internal static class GlobalValues
	{
		public const string ERROR_PROPERTY_NULL = "The value is null and in an invalid state.";
		public const string ERROR_PROPERTY_SETNULL = "Cannot set value to null.";
		public const string ERROR_CONCURRENCY_FAILURE = "Concurrency failure";
		public const string ERROR_CONSTRAINT_FAILURE = "Constraint failure";
		public const string ERROR_DATA_TOO_BIG = "The data '{0}' is too large for the {1} field which has a length of {2}.";
		public static readonly DateTime MIN_DATETIME = new DateTime(1753, 1, 1);
		public static readonly DateTime MAX_DATETIME = new DateTime(9999, 12, 31, 23, 59, 59);
		private const string INVALID_BUSINIESSOBJECT = "An invalid object of type 'IBusinessObject' was passed in. Perhaps a relationship was not enforced correctly.";

		internal static string SetValueHelperInternal(string newValue, bool fixLength, int maxDataLength)
		{
			string retval = null;
			if (newValue == null)
			{
				retval = null;
			}
			else
			{
				string v = newValue.ToString();
				if (fixLength)
				{
					int fieldLength = maxDataLength;
					if ((fieldLength > 0) && (v.Length > fieldLength)) v = v.Substring(0, fieldLength);
				}
				retval = v;
			}
			return retval;
		}

		internal static double? SetValueHelperDoubleNullableInternal(object newValue)
		{
			double? retval;
			if (newValue == null)
			{
				retval = null;
			}
			else
			{
				if (newValue is string)
				{
					retval = double.Parse((string)newValue);
				}
				else if (!(newValue is double?))
				{
					retval = double.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (double?)newValue;
			}
			return retval;
		}

		internal static double SetValueHelperDoubleNotNullableInternal(object newValue, string nullMessage)
		{
			double retval;
			if (newValue == null)
			{
				throw new Exception(nullMessage);
			}
			else
			{
				if (newValue is string)
				{
					retval = double.Parse((string)newValue);
				}
				else if (!(newValue is double))
				{
					retval = double.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (double)newValue;
			}
			return retval;
		}

		internal static DateTime? SetValueHelperDateTimeNullableInternal(object newValue)
		{
			DateTime? retval;
			if (newValue == null)
			{
				retval = null;
			}
			else
			{
				if (newValue is string)
				{
					retval = DateTime.Parse((string)newValue);
				}
				else if (!(newValue is DateTime?))
				{
					retval = DateTime.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (DateTime?)newValue;
			}
			return retval;
		}

		internal static DateTime SetValueHelperDateTimeNotNullableInternal(object newValue, string nullMessage)
		{
			DateTime retval;
			if (newValue == null)
			{
				throw new Exception(nullMessage);
			}
			else
			{
				if (newValue is string)
				{
					retval = DateTime.Parse((string)newValue);
				}
				else if (!(newValue is DateTime))
				{
					retval = DateTime.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (DateTime)newValue;
			}
			return retval;
		}

		internal static bool? SetValueHelperBoolNullableInternal(object newValue)
		{
			bool? retval;
			if (newValue == null)
			{
				retval = null;
			}
			else
			{
				if (newValue is string)
				{
					retval = bool.Parse((string)newValue);
				}
				else if (!(newValue is bool?))
				{
					retval = bool.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (bool?)newValue;
			}
			return retval;
		}

		internal static bool SetValueHelperBoolNotNullableInternal(object newValue, string nullMessage)
		{
			bool retval;
			if (newValue == null)
			{
				throw new Exception(nullMessage);
			}
			else
			{
				if (newValue is string)
				{
					retval = bool.Parse((string)newValue);
				}
				else if (!(newValue is bool))
				{
					retval = bool.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (bool)newValue;
			}
			return retval;
		}

		internal static int? SetValueHelperIntNullableInternal(object newValue)
		{
			int? retval;
			if (newValue == null)
			{
				retval = null;
			}
			else
			{
				if (newValue is string)
				{
					retval = int.Parse((string)newValue);
				}
				else if (!(newValue is int?))
				{
					retval = int.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (int?)newValue;
			}
			return retval;
		}

		internal static int SetValueHelperIntNotNullableInternal(object newValue, string nullMessage)
		{
			int retval;
			if (newValue == null)
			{
				throw new Exception(nullMessage);
			}
			else
			{
				if (newValue is string)
				{
					retval = int.Parse((string)newValue);
				}
				else if (!(newValue is int))
				{
					retval = int.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (int)newValue;
			}
			return retval;
		}

		internal static long? SetValueHelperLongNullableInternal(object newValue)
		{
			long? retval;
			if (newValue == null)
			{
				retval = null;
			}
			else
			{
				if (newValue is string)
				{
					retval = long.Parse((string)newValue);
				}
				else if (!(newValue is long?))
				{
					retval = long.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (long?)newValue;
			}
			return retval;
		}

		internal static long SetValueHelperLongNotNullableInternal(object newValue, string nullMessage)
		{
			long retval;
			if (newValue == null)
			{
				throw new Exception(nullMessage);
			}
			else
			{
				if (newValue is string)
				{
					retval = long.Parse((string)newValue);
				}
				else if (!(newValue is long))
				{
					retval = long.Parse(newValue.ToString());
				}
				else if (newValue is nHydrate.EFCore.DataAccess.IBusinessObject)
				{
					throw new Exception(INVALID_BUSINIESSOBJECT);
				}
				else
					retval = (long)newValue;
			}
			return retval;
		}

		internal static T PropertyGetterLambdaErrorHandler<T>(Func<T> func)
		{
			try
			{
				return func();
			}
			catch (System.Data.DBConcurrencyException dbcex) { throw new ConcurrencyException(GlobalValues.ERROR_CONCURRENCY_FAILURE, dbcex); }
			catch (System.Data.SqlClient.SqlException sqlexp) { if (sqlexp.Number == 547 || sqlexp.Number == 2627) throw new UniqueConstraintViolatedException(GlobalValues.ERROR_CONSTRAINT_FAILURE, sqlexp); else throw; }
			catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); throw; }
		}

		internal static void PropertySetterLambdaErrorHandler(System.Action action)
		{
			try
			{
				action();
			}
			catch (System.Data.DBConcurrencyException dbcex) { throw new ConcurrencyException(GlobalValues.ERROR_CONCURRENCY_FAILURE, dbcex); }
			catch (System.Data.SqlClient.SqlException sqlexp) { if (sqlexp.Number == 547 || sqlexp.Number == 2627) throw new UniqueConstraintViolatedException(GlobalValues.ERROR_CONSTRAINT_FAILURE, sqlexp); else throw; }
			catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); throw; }
		}

	}

	[System.CodeDom.Compiler.GeneratedCode("nHydrateModelGenerator", "5.0.1.100")]
	internal static class Extensions
	{
		public static bool Contains(this DataRelationCollection relationList, DataRelation relation)
		{
			foreach (DataRelation r in relationList)
			{
				int matches = 0;
				foreach (DataColumn c in r.ChildColumns)
				{
					if (relation.ChildColumns.Contains(c))
						matches++;
				}

				foreach (DataColumn c in r.ParentColumns)
				{
					if (relation.ParentColumns.Contains(c))
						matches++;
				}

				if (r.ChildColumns.Length == (matches * 2))
					return true;

			}
			return false;
		}
	}

}
